package com.dao;

import com.vo.Vote;

import java.sql.*;
import java.util.ArrayList;

public class VoteDao {

    private Connection conn=null;

    /**
     * 初始化数据库
     * @throws Exception
     */
    public void initConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        conn= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/topiao?allowMultiQueries=true&useUnicode=true&useSSL=false","root","123456");
    }

    //返回所有教师及其得票教
    public ArrayList getAllVotes() throws Exception {
        ArrayList al=new ArrayList();
        initConnection();
        String sql="select teacherno,teachername,votenumber from t_vote ";
        Statement stat=conn.createStatement();
        ResultSet rs=stat.executeQuery(sql);
        while (rs.next()){
            Vote vote=new Vote();
            vote.setTeacherno(rs.getString("teacherno"));
            vote.setTeachername(rs.getString("teachername"));
            vote.setVotenumber(rs.getInt("votenumber"));
            al.add(vote);
        }
        closeConnection();
        return al;
    }

    //修改某些教师的投票数
    public void updateVotes(String[] teacherno) throws Exception {
        initConnection();
        String sql="update t_vote set votenumber=votenumber+1 where teacherno=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        for (int i = 0; i < teacherno.length; i++) {
            ps.setString(1,teacherno[i]);
            ps.executeUpdate();
        }
        closeConnection();
    }

    //删除教师
    public void deleteVotes(String teacherno) throws Exception {
        initConnection();
        String sql="delete from  t_vote  where teacherno=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,teacherno);
        ps.executeUpdate();
        closeConnection();
    }

    //新增教师
    public void addVotes(String teacherno,String teachername) throws Exception {
        initConnection();
        String sql="insert into t_vote(teacherno,teachername,votenumber) values (?,?,0)";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,teacherno);
        ps.setString(2,teachername);
        ps.executeUpdate();
        closeConnection();
    }

    //修改教师
    public void upVotes(String teachername,String teacherno) throws Exception {
        initConnection();
        String sql="update t_vote set teachername=? where teacherno=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,teachername);
        ps.setString(2,teacherno);
        ps.executeUpdate();
        closeConnection();
    }


    private void closeConnection() throws Exception{
        conn.close();
    }


}
